import json
import os

import mysql.connector
from dotenv import load_dotenv

load_dotenv()


def get_sql_connection():
  host = os.getenv('DB_HOST')
  port = os.getenv('DB_PORT')
  username = os.getenv('DB_USERNAME')
  password = os.getenv('DB_PASSWORD')
  database = os.getenv('DB_DATABASE')

  if host is None:
    raise Exception("缺少环境变量：DB_HOST")
  if port is None:
    raise Exception("缺少环境变量：DB_PORT")
  if username is None:
    raise Exception("缺少环境变量：DB_USERNAME")
  if password is None:
    raise Exception("缺少环境变量：DB_PASSWORD")
  if database is None:
    raise Exception("缺少环境变量：DB_DATABASE")

  conn = mysql.connector.connect(
    host=host,
    port=port,
    user=username,
    password=password,
    database=database,
  )

  cursor = conn.cursor()

  def close():
    cursor.close()
    conn.close()

  return (conn, cursor, close)


def _main():
  (conn, cursor, close) = get_sql_connection()
  try:
    cursor.execute("""
                   select t1.id     as id,
                          t1.name   as name,
                          t1.cpu    as cpu,
                          t1.memory as memory,
                          t1.net    as net,
                          CAST(t1.year as CHAR) as year,
                          CAST(t1.price as CHAR) as price
                   from pl_sale_plans t1
                   """)
    rows = cursor.fetchall()
    print(rows)

    columns = [col[0] for col in cursor.description]

    rows = [dict(zip(columns, row)) for row in rows]

    print(
      json.dumps(rows, ensure_ascii=False, indent=2)
    )
  except mysql.connector.Error as err:
    print("数据库异常：", err)
  except Exception as err:
    print(err)
  finally:
    close()


_main()
